﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplicationTest
{
    /// <summary>
    /// 数据库信息查询示例： http://localhost:5516/Sql.aspx?SELECT * FROM 数据表1
    /// </summary>
    public partial class Sql : System.Web.UI.Page
    {
        /// <summary>
        /// 获取请求参数信息
        /// </summary>
        private String getParam(String LogName = "")
        {
            String Url = Request.Url.ToString();
            String param = "";
            if (Url.Contains("?"))
            {
                param = Url.Substring(Url.IndexOf("?") + 1);                // 获取参数信息

                if (LogName.Equals("")) LogName = this.GetType().Name;
                LogTool log = new LogTool(LogName);                         // 记录至log中
                log.WriteLine(param);
            }
            return param;
        }

        /// <summary>
        /// 页面载入
        /// </summary>
        protected void Page_Load(object sender, EventArgs e)
        {
            String queryString = getParam();                                  // 获取sql查询语句
            if (queryString.Equals("")) Response.Write("NULL");
            else
            {
                String data = Execute(queryString);
                Response.Write(data);
            }
        }

        #region 本地数据库操作逻辑

        /// <summary>
        /// 本地数据库连接串信息
        /// </summary>
        public static string connectionString = @"Data Source=.\JSQL2008;Initial Catalog=DataBase1;User ID=sa;Password=Sa12345789"; // 连接本地数据库DataBase1

        /// <summary>
        /// 连接数据库,执行sql语句
        /// connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NoteBook.mdf;Integrated Security=True;User Instance=True";  // 连接附加数据库
        /// connectionString = @"Data Source=.\JSQL2008;Initial Catalog=DataBase1;User ID=sa;Password=Sa12345789"; // 连接本地数据库DataBase1
        /// queryString = "SELECT * FROM 数据表1";
        /// </summary>
        public static String Execute(string queryString, string connectionString = null)
        {
            try
            {
                if (connectionString == null || connectionString.Equals("")) connectionString = Sql.connectionString;
                //string queryString =  "SELECT * FROM 数据表1";
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    SqlCommand command = new SqlCommand(queryString, connection);
                    SqlDataReader reader = command.ExecuteReader();

                    String jsonData = ToJson(reader);

                    connection.Close();

                    if (jsonData.Trim().Equals("")) jsonData = "success";
                    return jsonData;
                }
            }
            catch (Exception ex)
            {
                return "fail";
            }
        }

        /// <summary>
        /// DataReader转换为Json串
        /// </summary>
        public static string ToJson(SqlDataReader dataReader)
        {
            StringBuilder Builder = new StringBuilder();

            int rows = 0;
            while (dataReader.Read())
            {
                if (rows++ > 0) Builder.Append(",");

                // 行数据转Json
                Builder.Append("{");
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    if (i > 0) Builder.Append(",");

                    // 列名称
                    string strKey = dataReader.GetName(i);
                    strKey = "\"" + strKey + "\"";

                    // 列数据
                    Type type = dataReader.GetFieldType(i);
                    string strValue = dataReader[i].ToString();
                    strValue = String.Format(strValue, type).Trim();
                    if (type == typeof(string) || type == typeof(DateTime)) strValue = "\"" + strValue + "\"";

                    Builder.Append(strKey + ":" + strValue);
                }
                Builder.Append("}");
            }
            dataReader.Close();

            if (rows > 1) return "[" + Builder.ToString() + "]";
            else return Builder.ToString();
        }

        #endregion

    }
}